import pymysql
from common.get_env_config import GetEnvConfig
from common.get_log import log


class Mysql1:
    """
    操作mysql的类,连接单点登录数据库
    """

    def __init__(self):
        getenvconfig = GetEnvConfig()
        """
        初始化mysql的conn对象，连接数据库
        """
        # 通过配置文件获取数据库的host，port，username，password，charset，databaes
        host = getenvconfig.get_env_para("host1")
        # 从配置文件获取的值是str，需要转化成int
        port = int(getenvconfig.get_env_para("database_port1"))
        user = getenvconfig.get_env_para("database_user1")
        password = getenvconfig.get_env_para("database_password1")
        charset = getenvconfig.get_env_para("charset")
        database = getenvconfig.get_env_para("database1")
        # 当无法连接数据库，走异常处理处理
        try:
            self.conn = pymysql.connect(host=host, port=port, user=user,
                                        password=password, charset=charset, database=database)
        except Exception as e:
            log.error(f"无法登陆数据库，错误原因：{e}")

    def select(self, query):
        """
        运行mysql的select语句
        :param query: select语句
        :return: select_data：返回全部的select语句的数据
        """
        log.info(f"select语句为：{query}")
        try:
            # 定义游标，并通过execute执行sql语句
            cur = self.conn.cursor()
            num = cur.execute(query)
            # 提交游标数据
            self.conn.commit()
            # fetchall读取游标中的所有select数据
            if num > 1:  # 如果查询结果大于1,返回结果转换为列表套列表
                result = cur.fetchall()
                X = []
                for m in result:
                    x = []
                    for n in m:
                        x.append(n)
                    X.append(x)
                log.info("数据查询成功")
                return X
            elif num == 1: # 查询结果只有一条
                for i in cur.fetchall():
                    for j in i:
                        if len(i) > 1:  # 如果该元组元素数量大于1返回整个元组
                            log.info("数据查询成功")
                            return i
                        else:  # 如果该元组元素数量等于1返回该元素
                            log.info("数据查询成功")
                            return j
            elif num == 0: #查询结果为0
                log.info("数据查询无结果")
                return None
        except Exception as e:
            log.error(f"select语句错误，错误原因是：{e}")


    def insert(self, query):
        """
        运行mysql的select语句
        :param query: insert语句
        """
        log.info(f"insert语句为：{query}")
        try:
            # 定义游标，并通过execute执行insert语句
            cur = self.conn.cursor()
            cur.execute(query)
            # insert执行成功后commit提交数据
            cur.execute("commit")
            log.info(f"数据插入成功")
        except Exception as e:
            log.error(f"insert 语句错误，原因是{e}")
            # insert失败后rollback回滚数据
            cur.execute("rollback")


    def delete(self, query):
        """
        运行mysql的delete语句
        :param query: delete语句
        """
        log.info(f"delete语句为：{query}")
        try:
            # 定义游标，并通过execute执行delete语句
            cur = self.conn.cursor()
            cur.execute(query)
            # delete执行成功后commit提交数据
            cur.execute("commit")
            log.info("数据删除成功")
        except Exception as e:
            log.error(f"delete语句失败，原因：{e}")
            # delete失败后rollback回滚数据
            cur.execute("rollback")

    def update(self, query):
        """
        运行mysql的delete语句
        :param query: delete语句
        """
        log.info(f"update语句为：{query}")
        try:
            # 定义游标，并通过execute执行delete语句
            cur = self.conn.cursor()
            cur.execute(query)
            # delete执行成功后commit提交数据
            cur.execute("commit")
            log.info("数据更新成功")
        except Exception as e:
            log.error(f"update语句失败，原因：{e}")
            # delete失败后rollback回滚数据
            cur.execute("rollback")


class Mysql2:
    """
    操作mysql的类,连接电子班牌数据库
    """

    def __init__(self):
        getenvconfig = GetEnvConfig()
        """
        初始化mysql的conn对象，连接数据库
        """
        # 通过配置文件获取数据库的host，port，username，password，charset，database
        host = getenvconfig.get_env_para("host2")
        # 从配置文件获取的值是str，需要转化成int
        port = int(getenvconfig.get_env_para("database_port2"))
        user = getenvconfig.get_env_para("database_user2")
        password = getenvconfig.get_env_para("database_password2")
        charset = getenvconfig.get_env_para("charset")
        database = getenvconfig.get_env_para("database2")
        # 当无法连接数据库，走异常处理处理
        try:
            self.conn = pymysql.connect(host=host, port=port, user=user,
                                        password=password, charset=charset, database=database)
        except Exception as e:
            log.error(f"无法登陆数据库，错误原因：{e}")

    def select(self, query):
        """
        运行mysql的select语句
        :param query: select语句
        :return: select_data：返回全部的select语句的数据
        """
        log.info(f"select语句为：{query}")
        try:
            # 定义游标，并通过execute执行sql语句
            cur = self.conn.cursor()
            num = cur.execute(query)
            # 提交游标数据
            self.conn.commit()
            # fetchall读取游标中的所有select数据
            if num > 1:  # 如果查询结果大于1,返回结果转换为列表套列表
                result = cur.fetchall()
                print(result)
                X = []
                for m in result:
                    x = []
                    for n in m:
                        x.append(n)
                    X.append(x)
                log.info("数据查询成功")
                return X
            elif num == 1:  # 查询结果只有一条
                for i in cur.fetchall():
                    for j in i:
                        if len(i) > 1:  # 如果该元组元素数量大于1返回整个元组
                            return i
                        else:  # 如果该元组元素数量等于1返回该元素
                            log.info("数据查询成功")
                            return j
            elif num == 0:  # 查询结果为0
                log.info("数据查询无结果")
                return None
        except Exception as e:
            log.error(f"select语句错误，错误原因是：{e}")

    def insert(self, query):
        """
        运行mysql的select语句
        :param query: insert语句
        """
        log.info(f"insert语句为：{query}")
        try:
            # 定义游标，并通过execute执行insert语句
            cur = self.conn.cursor()
            cur.execute(query)
            # insert执行成功后commit提交数据
            cur.execute("commit")
            log.info(f"数据插入成功")
        except Exception as e:
            log.error(f"insert 语句错误，原因是{e}")
            # insert失败后rollback回滚数据
            cur.execute("rollback")

    def delete(self, query):
        """
        运行mysql的delete语句
        :param query: delete语句
        """
        log.info(f"delete语句为：{query}")
        try:
            # 定义游标，并通过execute执行delete语句
            cur = self.conn.cursor()
            cur.execute(query)
            # delete执行成功后commit提交数据
            cur.execute("commit")
            log.info("数据删除成功")
        except Exception as e:
            log.error(f"delete语句失败，原因：{e}")
            # delete失败后rollback回滚数据
            cur.execute("rollback")

    def update(self, query):
        """
        运行mysql的delete语句
        :param query: delete语句
        """
        log.info(f"update语句为：{query}")
        try:
            # 定义游标，并通过execute执行delete语句
            cur = self.conn.cursor()
            cur.execute(query)
            # delete执行成功后commit提交数据
            cur.execute("commit")
            log.info("数据更新成功")
        except Exception as e:
            log.error(f"update语句失败，原因：{e}")
            # delete失败后rollback回滚数据
            cur.execute("rollback")


if __name__ == "__main__":
    a = Mysql1()
    print(a.select('select * from t_auth_user'))
